package oa.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import oa.dbcon.DBConnection;
import oa.lyk.service.Personoffice.Page;
import oa.pojo.Record;
import oa.view.RecordView;

public class RecordDao {
	public Page query(int currentPage,String name) {
		Page page = new Page();
		page.setCurrentPage(currentPage);

		List<RecordView> viewList = new ArrayList<RecordView>();
		StringBuffer sql;
		if(name ==null || "".equals(name)){
			sql = new StringBuffer("select v.*,row_number() over(order " +
					"by to_number(record_id)) rid from (select * from record_View)" +
					" v where status=1");
		}else{
			sql = new StringBuffer(
			"select v.*,row_number() over(order " +
			"by to_number(record_id)) rid from " +
			"(select * from record_View) v where status=1 and user_name=");
			sql.append("'").append(name).append("'");
		}
		StringBuffer pageSql = new StringBuffer(sql);
		pageSql.insert(0, "select * from (");
		pageSql.append(") where rid>=").append(
				(currentPage - 1) * Page.PAGE_SIZE + 1).append(" and rid<=")
				.append((currentPage - 1) * Page.PAGE_SIZE + Page.PAGE_SIZE);

		Connection con = DBConnection.getDBConnection();
		try {
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(pageSql.toString());
			while (rs.next()) {
				RecordView view = new RecordView();
				view.setRecordid(rs.getString(1));
				view.setUserId(rs.getString(2));
				view.setUserName(rs.getString(3));
				view.setOldDeptId(rs.getString(4));
				view.setNewDeptId(rs.getString(5));
				view.setOldJob(rs.getString(6));
				view.setNewJob(rs.getString(7));
				view.setOperateDate(rs.getString(8));
				view.setStatus(rs.getString(9));
				view.setOldDept(rs.getString(10));
				view.setNewDept(rs.getString(11));
				viewList.add(view);
			}
			rs.close();
			st.close();
			page.setRecordView(viewList);

			StringBuffer totalSql = new StringBuffer(sql);
			totalSql.insert(0, "select count(*) from (");
			totalSql.append(")");
			st = con.createStatement();

			int lineNum = 0;
			rs = st.executeQuery(totalSql.toString());
			if (rs.next()) {
				lineNum = rs.getInt(1);
			}
			int totalPage = (lineNum + Page.PAGE_SIZE - 1) / Page.PAGE_SIZE;
			rs.close();
			st.close();
			page.setTotalPage(totalPage);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBConnection.closeDBConnection(con);
		return page;
	}
	public boolean del(String id){
		boolean flag = false;
		String sql="update t_record set status=0 where record_id=?";
		Connection con = DBConnection.getDBConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setString(1, id);
			if(pst.executeUpdate()>0){
				flag=true;
			}
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBConnection.closeDBConnection(con);
		return flag;
	}
	
	public boolean save(Record r){
		boolean flag = false;
		String sql="insert into t_record values(seq_record.nextVal,?,?,?,?,?,?,?,'1')";
		Connection con = DBConnection.getDBConnection();
		try {
			PreparedStatement pst = con.prepareStatement(sql);
			pst.setString(1, r.getUserId());
			pst.setString(2, r.getUserName());
			pst.setString(3, r.getOldDeptId());
			pst.setString(4, r.getNewDeptId());
			pst.setString(5, r.getOldJob());
			pst.setString(6, r.getNewJob());
			pst.setString(7, r.getOperateDate());
			if(pst.executeUpdate()>0){
				flag = true;
			}
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBConnection.closeDBConnection(con);
		return flag;
	}
}
